﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Common;

namespace WebNhaCungCap.app.DAO
{
    public class Dataprovider
    {
        private SqlCommand cmd = new SqlCommand();
        private SqlConnection cnn = new SqlConnection();


        public Dataprovider()
        {
            cnn.ConnectionString = ConfigurationManager.ConnectionStrings["NHACUNGCAP"].ToString();
            cmd.Connection = cnn;
        }

        public void Connect()
        {
            cnn.Open();
        }

        public void Disconnect()
        {
            cnn.Close();
        }

        public DataTable ExecuteQuery(string strSQL)
        {
            Connect();
            cmd.CommandText = strSQL;
            SqlDataReader sdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(sdr);
            sdr.Close();
            Disconnect();
            return dt;
        }

        public void ExecuteNonQuery(string strSQL)
        {
            Connect();
            cmd.CommandText = strSQL;
            cmd.ExecuteNonQuery();
            Disconnect();
        }
        public SqlDataReader EXEquery(string sql)
        {
            Connect();
            cmd.CommandText = sql;
            SqlDataReader sdr = cmd.ExecuteReader();
            Disconnect();
            return sdr;

        }

        public void INSERT_TAILIEUDIENTU(DTO.TAILIEUDIENTU tl)
        {
            cmd.CommandText = "INSERT_TAILIEUDIENTU";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@tenTL", SqlDbType.NVarChar)).Value =tl.TenTaiLieu ;
            cmd.Parameters.Add(new SqlParameter("@maLTL", SqlDbType.Int)).Value = tl.MaLoaiTaiLieu;
            cmd.Parameters.Add(new SqlParameter("@tacG", SqlDbType.NVarChar)).Value = tl.TacGia;
            cmd.Parameters.Add(new SqlParameter("@namXB", SqlDbType.DateTime)).Value = tl.NamXuatBan;
            cmd.Parameters.Add(new SqlParameter("@dungL", SqlDbType.NVarChar)).Value = tl.DungLuong;
            cmd.Parameters.Add(new SqlParameter("@duongD", SqlDbType.NVarChar)).Value = tl.DuongDan;
            cmd.Parameters.Add(new SqlParameter("@donG", SqlDbType.Float)).Value = tl.DonGia;
            cmd.Parameters.Add(new SqlParameter("@soLT", SqlDbType.Int)).Value = tl.SoLuongTon;
            cmd.Parameters.Add(new SqlParameter("@ngayT", SqlDbType.DateTime)).Value = tl.NgayThem;
            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }
        public void UPDATE_TAILIEUDIENTU(DTO.TAILIEUDIENTU tl)
        {
            cmd.CommandText = "UPDATE_TAILIEUDIENTU";
            cmd.CommandType = CommandType.StoredProcedure;

            //--
            DbParameter param = cmd.CreateParameter();
            param.ParameterName = "@maTL";
            param.DbType = DbType.Int32;
            param.Value = tl.MaLoaiTaiLieu;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@tenTL";
            param.DbType = DbType.String;
            param.Value = tl.TenTaiLieu;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@maLTL";
            param.DbType = DbType.Int32;
            param.Value = tl.MaLoaiTaiLieu;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@tacG";
            param.DbType = DbType.String;
            param.Value = tl.TacGia;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@namXB";
            param.DbType = DbType.DateTime;
            param.Value = tl.NamXuatBan;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@dungL";
            param.DbType = DbType.String;
            param.Value = tl.DungLuong;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@duongD";
            param.DbType = DbType.String;
            param.Value = tl.DuongDan;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@donG";
            param.DbType = DbType.Double;
            param.Value = tl.DonGia;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@soLT";
            param.DbType = DbType.Int32;
            param.Value = tl.SoLuongTon;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@ngayT";
            param.DbType = DbType.DateTime;
            param.Value = tl.NgayThem;
            cmd.Parameters.Add(param);

            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        public void INSERT_HETHONGBANLE(DTO.HETHONGBANLE ht)
        {
            cmd.CommandText = "INSERT_HETHONGBANLE";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@tenHT", SqlDbType.NVarChar)).Value = ht.TenHeThong;
            cmd.Parameters.Add(new SqlParameter("@diaC", SqlDbType.NVarChar)).Value = ht.DiaChi;
            cmd.Parameters.Add(new SqlParameter("@dienT", SqlDbType.NVarChar)).Value = ht.DienThoai;
            cmd.Parameters.Add(new SqlParameter("@nguoiLH", SqlDbType.NVarChar)).Value = ht.NguoiLienHe;
            cmd.Parameters.Add(new SqlParameter("@tenDN", SqlDbType.NVarChar)).Value = ht.TenDangNhap;
            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        public void UPDATE_HETHONGBANLE(DTO.HETHONGBANLE ht)
        {
            cmd.CommandText = "UPDATE_HETHONGBANLE";
            cmd.CommandType = CommandType.StoredProcedure;

            //--
            DbParameter param = cmd.CreateParameter();
            param.ParameterName = "@maHT";
            param.DbType = DbType.Int32;
            param.Value = ht.MaHeThong;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@tenHT";
            param.DbType = DbType.String;
            param.Value = ht.TenHeThong;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@diaC";
            param.DbType = DbType.String;
            param.Value = ht.DiaChi;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@dienT";
            param.DbType = DbType.String;
            param.Value = ht.DienThoai;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@nguoiLH";
            param.DbType = DbType.String;
            param.Value = ht.NguoiLienHe;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@tenDN";
            param.DbType = DbType.String;
            param.Value = ht.TenDangNhap;
            cmd.Parameters.Add(param);

            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        public void INSERT_NGUOIDUNG(DTO.NGUOIDUNG nd)
        {
            cmd.CommandText = "INSERT_NGUOIDUNG";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@tenDN", SqlDbType.NVarChar)).Value = nd.TenDangNhap;
            cmd.Parameters.Add(new SqlParameter("@matK", SqlDbType.NVarChar)).Value = nd.MatKhau;
            cmd.Parameters.Add(new SqlParameter("@maQ", SqlDbType.NVarChar)).Value = nd.MaQuyen;
            
            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        public void UPDATE_NGUOIDUNG(DTO.NGUOIDUNG nd)
        {
            cmd.CommandText = "UPDATE_NGUOIDUNG";
            cmd.CommandType = CommandType.StoredProcedure;

            //--
            DbParameter param = cmd.CreateParameter();
            param.ParameterName = "@tenDN";
            param.DbType = DbType.String;
            param.Value = nd.TenDangNhap;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@matK";
            param.DbType = DbType.String;
            param.Value = nd.MatKhau;
            cmd.Parameters.Add(param);

            //--
            param = cmd.CreateParameter();
            param.ParameterName = "@maQ";
            param.DbType = DbType.String;
            param.Value = nd.MaQuyen;
            cmd.Parameters.Add(param);

            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        public void DELETE_QUYENHAN(int maQ)
        {
            cmd.CommandText = "DELETE_QUYENHAN";
            cmd.CommandType = CommandType.StoredProcedure;

            DbParameter param = cmd.CreateParameter();
            param.ParameterName = "@maQ";
            param.DbType = DbType.Int32;
            param.Value = maQ;
            cmd.Parameters.Add(param);

            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        public void DELETE_LOAITAILIEU(int maL)
        {
            cmd.CommandText = "DELETE_LOAITAILIEU";
            cmd.CommandType = CommandType.StoredProcedure;

            DbParameter param = cmd.CreateParameter();
            param.ParameterName = "@maL";
            param.DbType = DbType.Int32;
            param.Value = maL;
            cmd.Parameters.Add(param);

            Connect();
            cmd.ExecuteNonQuery();
            Disconnect();
        }

        internal void DELETE_QUYENHAN(string p, int maQH)
        {
            throw new NotImplementedException();
        }

        internal void DELETE_LOAITAILIEU(string p, int maLoaiTaiLieu)
        {
            throw new NotImplementedException();
        }
    }
}